#!/usr/bin/perl

use strict;
use DBI;
use CGI;

my $dsn = 'DBI:SQLite:dbname=../oneseg24/epg.db';
my $dbh = DBI->connect($dsn, undef, undef, {RaiseError => 1, AutoCommit => 0});

my $q = new CGI;
my $action = $q->param('action');

if ($action eq 'regist') {
	my @binds = $q->param('bind');

	my $sql = <<END;
update
	da_channel
set
	dc_network_id = null,
	dc_service_id = null
END

	my $udh = $dbh->prepare($sql);
	my $rv = $udh->execute();

	foreach my $bind (@binds) {
		my($dv_original_network_id, $dv_service_id, $dc_id) = split(/:/, $bind);

		my $sql = <<END;
update
	da_channel
set
	dc_network_id = ?,
	dc_service_id = ?
where
	dc_id = ?
END

		my $udh = $dbh->prepare($sql);
		my $rv = $udh->execute($dv_original_network_id, $dv_service_id, $dc_id);
	}

	$dbh->commit();

	print "Location: epg.cgi\n\n";
	exit;
}

my $sql = <<END;
select
	dc_id,
	dc_ch,
	dc_name,
	dc_network_id,
	dc_service_id
from
	da_channel
order by
	dc_id
END

my $sth = $dbh->prepare($sql);
my $rv = $sth->execute();
my $channel = $sth->fetchall_arrayref();

print "Content-type: text/html\n\n";

print <<END;
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=utf-8">
<title>OneSeg24 - setup</title>
</head>
<body bgcolor="#000000" text="#ffffff" link="#ffff00" vlink="#ffff00">
<form action="$ENV{SCRIPT_NAME}" method="post">
<input type="hidden" name="action" value="regist">
END

my $sql = <<END;
select
	dv_original_network_id,
	dv_service_id
from
	da_event
group by
	dv_original_network_id,
	dv_service_id
END

my $sth = $dbh->prepare($sql);
my $rv = $sth->execute();

while (my $ref = $sth->fetch()) {
	my($dv_original_network_id, $dv_service_id) = @$ref;

	print <<END;
<select name="bind">
<option value=""></option>
END

	foreach my $ref (@$channel) {
		my($dc_id, $dc_ch, $dc_name, $dc_network_id, $dc_service_id) = @$ref;
		my $value = join(':', $dv_original_network_id, $dv_service_id, $dc_id);

		if ($dc_network_id == $dv_original_network_id && $dc_service_id == $dv_service_id) {
			print "<option value=\"$value\" selected>$dc_ch $dc_name</option>\n";
		} else {
			print "<option value=\"$value\">$dc_ch $dc_name</option>\n";
		}
	}

	print <<END;
</select>
<table>
<tr bgcolor="#666688">
<td>放送日時</td>
<td>番組名</td>
</tr>
END

	&event($dv_original_network_id, $dv_service_id);

	print <<END;
</table>
<br>
END

}

sub event {
	my($dv_original_network_id, $dv_service_id) = @_;

	my $sql = <<END;
select
	dv_start_time,
	dv_name
from
	da_event
where
	dv_original_network_id = ?
and
	dv_service_id = ?
limit
	10
END

	my $sth = $dbh->prepare($sql);
	my $rv = $sth->execute($dv_original_network_id, $dv_service_id);

	while (my $ref = $sth->fetch()) {
		my($dv_start_time, $dv_name) = @$ref;

		print <<END;
<tr bgcolor="#666666">
<td>$dv_start_time</td>
<td>$dv_name</td>
</tr>
END

	}
}

print <<END;
<input type="submit" value="登録">
</form>
</body>
</html>
END

$dbh->disconnect();

